// config/db.js
const mysql = require('mysql2/promise');

// 设置环境变量
process.env.DB_HOST = 'localhost';
process.env.DB_USER = 'root';
process.env.DB_PASSWORD = 'rjxy123456';
process.env.DB_NAME = 'bangninsheng';
process.env.JWT_SECRET = 'help-u-save-secret-key';
process.env.NODE_ENV = 'development';

const pool = mysql.createPool({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0,
    timezone: '+08:00',
    // 连接超时配置
    connectTimeout: 10000,
    multipleStatements: true,
    dateStrings: true
});

// 测试数据库连接
async function testConnection(retries = 3) {
    for (let i = 0; i < retries; i++) {
        try {
            // 获取一个连接
            const connection = await pool.getConnection();
            console.log('✅ 数据库连接成功！');

            // 执行简单查询验证
            const [rows] = await connection.execute('SELECT 1 + 1 AS result');
            console.log('测试查询结果:', rows[0].result);

            // 检查必要的表是否存在
            const [tables] = await connection.execute(`
                SELECT TABLE_NAME 
                FROM information_schema.TABLES 
                WHERE TABLE_SCHEMA = ?
            `, [process.env.DB_NAME]);

            const requiredTables = ['users', 'stores', 'coupons', 'orders'];
            const missingTables = requiredTables.filter(
                table => !tables.some(t => t.TABLE_NAME === table)
            );

            if (missingTables.length > 0) {
                console.warn('⚠️ 警告：以下数据表不存在：', missingTables.join(', '));
                console.warn('请确保已执行 database.sql 初始化脚本');
            }

            // 释放连接
            connection.release();
            return true;
        } catch (error) {
            console.error(`❌ 数据库连接失败 (尝试 ${i + 1}/${retries}):`, error.message);
            if (i === retries - 1) {
                console.error('请检查：');
                console.error('- MySQL 服务是否已启动');
                console.error('- 数据库配置是否正确');
                console.error(`  HOST: ${process.env.DB_HOST || 'localhost'}`);
                console.error(`  USER: ${process.env.DB_USER}`);
                console.error(`  DATABASE: ${process.env.DB_NAME}`);
                console.error('- 数据库和用户权限是否正确');
                throw error;
            }
            // 等待后重试
            await new Promise(resolve => setTimeout(resolve, 2000));
        }
    }
    return false;
}

// 启动时执行测试
testConnection().catch(error => {
    console.error('数据库连接测试失败:', error);
    process.exit(1);
});

// 添加错误处理
pool.on('error', (err) => {
    console.error('数据库池错误:', err);
    if (err.code === 'PROTOCOL_CONNECTION_LOST') {
        console.error('与数据库的连接丢失');
    }
});

module.exports = pool;